#!/usr/bin/env bash

-- 备注 视图不能索引,也不能有关联的触发器、默认值或规则
-- 备注 视图不能索引,也不能有关联的触发器、默认值或规则
-- 业务要求，所有的数据要求统计 3月20日之前的(包含两端的当天)。只有注销账号是这个范围，上个月21号，到本月20。
-- 所以这里为了保证灵活性的操作，所有的数据要求自统计不做时间限制(但会每天保存计算结果)； 注销账号的时间是暂时不变动，依旧是上个月21号，到本月20

-- 1）视图 归属地的分组表
create or replace view oaAduitCollect_minTimebelonging  as
  (SELECT minTimeBELONGING from oaAudit_oaV1_userClean GROUP by minTimeBELONGING)  -- 明细表
  UNION
  (SELECT  minTimeBELONGING from oaAudit_oaV1_userClean_multisign_white GROUP by minTimeBELONGING)  -- 一人多号+白名单
  UNION
  (SELECT  minTimeBELONGING from oaAudit_oaV1_userClean_phoneSharing_white GROUP by minTimeBELONGING)  -- 共用手机号+白名单
  UNION
  (SELECT  BELONGING from oaAudit_oa_logoffClean GROUP by BELONGING);
-- 离线表 -- 由于业务要求，这里的注销账号就统一按照最小时间的归属地址，来处理


-- 2）视图 明细表汇总
create  or  replace view   oaAduitCollect_oaV1_userClean  as
SELECT
  minTimeBELONGING ,
  COUNT(*) as "oa明细总数" ,
  SUM( IF ( ACC_TYPE_NAME = '合同制', 1, 0 ) ) as "合同制",
  SUM( IF ( ACC_TYPE_NAME = '非合同制', 1, 0 ) ) as "非合同制",
  SUM( IF ( ACC_TYPE_NAME = '临时账号', 1, 0 ) ) as "临时账号",
  SUM( IF ( ACC_TYPE_NAME = '合作伙伴', 1, 0 ) ) as "合作伙伴"
from oaAudit_oaV1_userClean
-- WHERE date_format(CREATE_TIME,'%Y%m%d') <=  20230320
GROUP by minTimeBELONGING ;

-- SELECT * FROM oaAudit_oaV1_userClean
-- WHERE date_format(CREATE_TIME,'%Y%m%d') <=  20230320
-- ORDER BY CREATE_TIME desc


-- 3）视图 一人多号白名单汇总
create  or  replace view   oaAduitCollect_oaV1_userClean_multisign_white as
SELECT
  minTimeBELONGING,
  COUNT(*) as "一人多号总数" ,
  sum( if(  WHITE = '一人多号账号白名单', 1, 0 ) ) as "一人多号白名单"
from oaAudit_oaV1_userClean_multisign_white
-- WHERE date_format(CREATE_TIME,'%Y%m%d') <=  20230320
GROUP by minTimeBELONGING
ORDER by minTimeBELONGING desc ;

-- SELECT * FROM oaAudit_oaV1_userClean
-- WHERE date_format(CREATE_TIME,'%Y%m%d') <=  20230320
-- ORDER BY CREATE_TIME desc

-- 4）视图 共用手机号白名单
create  or  replace view   oaAduitCollect_oaV1_userClean_phoneSharing_white  as
SELECT
  minTimeBELONGING,
  COUNT(*) as "共用手机号总数" , -- COUNT(DISTINCT pid ) 这个是统计'人数'的
  sum( if(  WHITE = '共用手机号账号白名单', 1, 0 ) ) as "共用手机号白名单" -- count( DISTINCT if(  WHITE = '共用手机号账号白名单', pid, null ) ) , 这个是统计'人数白名单'的
from oaAudit_oaV1_userClean_phoneSharing_white
-- WHERE date_format(CREATE_TIME,'%Y%m%d') <=  20230320
GROUP by minTimeBELONGING
ORDER by minTimeBELONGING desc;

-- SELECT * FROM oaAudit_oaV1_userClean_phoneSharing_white
-- WHERE date_format(CREATE_TIME,'%Y%m%d') <=  20230320
-- ORDER BY CREATE_TIME desc

-- 5）视图 注销账号
-- select date_format(DATE_SUB(CURDATE(), INTERVAL 1 month),'%Y-%m') -- 2023-02
-- select date_format(CURDATE(),'%Y-%m') -- 2023-03
create  or  replace view   oaAduitCollect_oa_logoffClean  as
SELECT
  BELONGING , -- 这里的注销账号就统一按照最小时间的归属地址，来处理
  COUNT(*) as "近期注销总数"
from oaAudit_oa_logoffClean
WHERE concat(date_format(DATE_SUB(CURDATE(), INTERVAL 1 month),'%Y-%m'),'-21') <= date_format(LOGOFF_TIME,'%Y-%m-%d')
  and date_format(LOGOFF_TIME,'%Y-%m-%d')  <= concat(date_format(CURDATE(),'%Y-%m'),'-20') -- 筛选上个月21号，到本月20的数据
GROUP by BELONGING
ORDER by BELONGING desc;
-- SELECT * FROM oaAduitCollect_oa_logoffClean;


-- 6）对上面的所有视图进行 关联拼接，不同的汇总表进行拼接
DELETE FROM oaAudit_collect;
INSERT into  oaAudit_collect(最小时间归属地址,oa明细总数,合同制,非合同制,合作伙伴,临时账号,一人多号总数,一人多号白名单,共用手机号总数,共用手机号白名单,近期注销总数)
SELECT
  res1.minTimeBELONGING as 最小时间归属地址,
  res2.`oa明细总数`,
  res2.`合同制`,
  res2.`非合同制`,
  res2.`合作伙伴`,
  res2.`临时账号`,
  ifNULL(res3.`一人多号总数`,0) as 一人多号总数,
  ifNULL(res3.`一人多号白名单`,0) as 一人多号白名单,
  ifNULL(res4.`共用手机号总数`,0) as 共用手机号总数,
  ifNULL(res4.`共用手机号白名单`,0) as 共用手机号白名单,
  ifNULL(res5.`近期注销总数`,0) as 近期注销总数
FROM
  oaAduitCollect_minTimebelonging res1
    LEFT JOIN
  oaAduitCollect_oaV1_userClean res2  on res1.minTimeBELONGING = res2.minTimeBELONGING
    LEFT JOIN
  oaAduitCollect_oaV1_userClean_multisign_white res3  on res1.minTimeBELONGING = res3.minTimeBELONGING
    LEFT JOIN
  oaAduitCollect_oaV1_userClean_phoneSharing_white res4 on res1.minTimeBELONGING = res4.minTimeBELONGING
    LEFT JOIN
  oaAduitCollect_oa_logoffClean res5 on res1.minTimeBELONGING = res5.BELONGING ;
-- 由于业务要求，这里的注销账号就统一按照最小时间的归属地址，来处理

-- 7）删除之前的临时视图
drop view  oaAduitCollect_minTimebelonging;
drop view  oaAduitCollect_oaV1_userClean;
drop view  oaAduitCollect_oaV1_userClean_multisign_white;
drop view  oaAduitCollect_oaV1_userClean_phoneSharing_white;
drop view  oaAduitCollect_oa_logoffClean;
